package com.tjtt.gwm.dao.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.tjtt.gwm.dao.ReportUpdateDao;
import com.tjtt.tdm.base.BaseHibernateDao;
import com.tjtt.tdm.base.PageResult;
import com.tjtt.tdm.base.StringUtil;
import com.tjtt.tdm.flow.bean.FlowTaskInfo;

public class ReportUpdateDaoImpl extends BaseHibernateDao implements ReportUpdateDao{

	@Override
	public void saveObject(Object object) {
		this.getHibernateTemplate().save(object);
	}

	@Override
	public Object getDataById(Class clazz, Long id) {
		return this.getHibernateTemplate().get(clazz, id);
	}

	@Override
	public void updateObject(Object object) {
		this.getHibernateTemplate().update(object);
	}

	/*
	 * 获取FlowTaskInfo对象
	 * 
	 */
	public FlowTaskInfo getFlowTaskInfo(String flowTaskId){
		String sql = "select t.id from FLOW_TASK_INFO t where t.flow_task_id="+flowTaskId;
		Query query = getSession().createSQLQuery(sql);
		List list = query.list();
		if(null != list && list.size()>0){
			String flowtaskinfoId = list.get(0).toString();
			return this.getHibernateTemplate().get(FlowTaskInfo.class, Long.parseLong(flowtaskinfoId));
		}else{
			return null;
		}
		
	}
	/*
	 * 保存FlowTaskInfo对象
	 * 
	 */
	public void updateFlowTaskInfo(FlowTaskInfo flowTaskInfo){
		this.getHibernateTemplate().update(flowTaskInfo);
	}

	@Override
	public PageResult queryReportData(Map<String, String> filterMap) {
		StringBuffer queryCountString = new StringBuffer();
		StringBuffer queryString = new StringBuffer();
		queryCountString.append("select count(id) from (" +
				"select t.id,f.id as taskid,f.flow_name,t.renwuname,t.weituodanshibieno,t.lianxiren,t.shenqingdate,t.shuangfangyuedingdate " +
				" from GWM_WEITUODAN t left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_WEITUODAN' where f.state != '5' and f.flow_state_id='end' ");
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("userid") && !"".equals(filterMap.get("userid"))){
				queryCountString.append(" and t.bianzhirenyuan = '"+filterMap.get("userid")+"'");
			}
		}
		queryCountString.append(" union ");
		queryCountString.append("select t.id,f.id as taskid,f.flow_name,t.renwuname,t.weituodanshibieno,t.bianzhirenyuan,t.shenqingriqi,t.shuangfangyuedingriqi" +
        	    " from GWM_YANFAJIANCE t left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_YANFAJIANCE' where f.state != '5' and f.flow_state_id='end'");
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("userid") && !"".equals(filterMap.get("userid"))){
				queryCountString.append(" and t.bianzhirenyuanid = '"+filterMap.get("userid")+"'");
			}
		}
		queryCountString.append(" union ");
		queryCountString.append("select t.id,f.id as taskid,f.flow_name,t.T_TESTNAME,t.T_WEITUODANBH,t.T_LIANXIREN,t.T_SHENQINGRQ,t.T_YUEDINGRIQI " +
        	    " from GWM_CHANGGIJIANCE t left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_CHANGGIJIANCE' where f.state != '5' and f.flow_state_id='end' ");
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("userid") && !"".equals(filterMap.get("userid"))){
				queryCountString.append(" and t.t_weituoren = '"+filterMap.get("userid")+"'");
			}
		}
		queryCountString.append(" ) order by taskid desc" );
		//查字段
		queryString.append("select * from (" +
				"select t.id,f.id as taskid,f.flow_name,f.flow_type_id,i.id as taskinfoid,t.renwuname,t.weituodanshibieno,t.lianxiren,t.shenqingdate,t.shuangfangyuedingdate,  " +
				" f.TABLE_NAME from GWM_WEITUODAN t left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_WEITUODAN'left join (select t1.id,t1.FLOW_TASK_ID,row_number() over(partition by t1.FLOW_TASK_ID order by t1.id) r from FLOW_TASK_INFO t1 ) i" +
				" on i.FLOW_TASK_ID = f.id and  i.r=1 where f.state != '5' and f.flow_state_id='end' ");
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("userid") && !"".equals(filterMap.get("userid"))){
				queryString.append(" and t.bianzhirenyuan = '"+filterMap.get("userid")+"'" );
			}
		}
		queryString.append(" union " );
		queryString.append("select t.id,f.id as taskid,f.flow_name,f.flow_type_id,i.id as taskinfoid,t.renwuname,t.weituodanshibieno,t.bianzhirenyuan,t.shenqingriqi,t.shuangfangyuedingriqi, " +
				"f.TABLE_NAME from GWM_YANFAJIANCE t left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_YANFAJIANCE' left join (select t1.id,t1.FLOW_TASK_ID,row_number() over(partition by t1.FLOW_TASK_ID order by t1.id) r from FLOW_TASK_INFO t1 ) i" +
				" on i.FLOW_TASK_ID = f.id and  i.r=1 where f.state != '5' and f.flow_state_id='end' ");
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("userid") && !"".equals(filterMap.get("userid"))){
				queryString.append(" and t.bianzhirenyuanid = '"+filterMap.get("userid")+"' "); 
			}
		}
		queryString.append(" union ");
		queryString.append("select t.id,f.id as taskid,f.flow_name,f.flow_type_id,i.id as taskinfoid,t.T_TESTNAME,t.T_WEITUODANBH,t.T_LIANXIREN,t.T_SHENQINGRQ,t.T_YUEDINGRIQI, " +
				" f.TABLE_NAME from GWM_CHANGGIJIANCE t left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_CHANGGIJIANCE' left join (select t1.id,t1.FLOW_TASK_ID,row_number() over(partition by t1.FLOW_TASK_ID order by t1.id) r from FLOW_TASK_INFO t1 ) i" +
				" on i.FLOW_TASK_ID = f.id and  i.r=1 where f.state != '5' and f.flow_state_id='end' ");
		if (filterMap != null && !filterMap.isEmpty()) {
			if(null != filterMap.get("userid") && !"".equals(filterMap.get("userid"))){
				queryString.append(" and t.t_weituoren = '"+filterMap.get("userid")+"' ");
			}
		}
		queryString.append(" ) order by taskid desc");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	@Override
	public PageResult queryReport(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM GWM_ITEM_SAMPLE T WHERE 1=1 ");
		queryString.append("select T.ID,T.T_ITEMNAME,O.ORG_NAME,T.T_DEPTLEADERNAME,T.T_JIACELEIBIE,T.T_JIANCEBIAOZHUN,T.T_JIANCEYUANNAME,T.T_YSJLFILEPATH,T.T_BGFILEPATH,T.T_REPORTSHIBIEH,I.T_BAOGAOTYPE,T1.FILE_NAME SAMPLE_FILE_NAME,T2.FILE_NAME ITEM_FILE_NAME, S.T_ANNEX T_ANNEX,I.T_ANNEX IT_ANNEX,f.FLOW_STATE_ID,f.FLOW_STATE_NAME  FROM GWM_ITEM_SAMPLE T  LEFT JOIN GWM_SAMPLE S  ON T.T_SAMPLEID=S.ID  LEFT JOIN TDM_ANNEX T1 ON S.T_ANNEX=T1.ID LEFT JOIN GWM_ITEM_FLOW I ON T.T_ITEMID = I.ID LEFT JOIN TDM_ANNEX T2 ON I.T_ANNEX=T2.ID LEFT JOIN SYS_ORGANIZATION O on T.T_DEPTID = O.ORG_ID left join FLOW_TASK f on t.T_TASKID=f.id  WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			
			queryCountString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
			queryString.append("and T.T_TASKID ='" + filterMap.get("taskId")+"'");
		}
		String sidx = (String) filterMap.get("sidx");
		String sord = (String) filterMap.get("sord");
		queryString.append(" order by " + sidx + " " + sord + " ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	@Override
	public List getweituodan(String id ) {
		String sql = "select t.id,f.id as taskid,i.id as taskinfoid,t.lianxiren,t.WEITUODANSHIBIENO,t.SHENQINGDANWEI,t.JIANCEMUDI  from GWM_WEITUODAN t" +
				" left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_WEITUODAN'left join (select t1.id,t1.FLOW_TASK_ID,row_number() over(partition by t1.FLOW_TASK_ID order by t1.id) r from FLOW_TASK_INFO t1 ) i" +
				" on i.FLOW_TASK_ID = f.id and  i.r=1 where t.id="+id+
				" union select t.id,f.id as taskid,i.id as taskinfoid,t.bianzhirenyuan,t.WEITUODANSHIBIENO,t.SHENQINGDANWEI,t.JIANCEMUDI from GWM_YANFAJIANCE t" +
				" left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_YANFAJIANCE' left join (select t1.id,t1.FLOW_TASK_ID,row_number() over(partition by t1.FLOW_TASK_ID order by t1.id) r from FLOW_TASK_INFO t1 ) i" +
				" on i.FLOW_TASK_ID = f.id and  i.r=1 where t.id="+id+
				" union select t.id,f.id as taskid,i.id as taskinfoid,t.T_LIANXIREN,t.T_WEITUODANBH,t.T_SHENQINGDW,t.T_MUDI from GWM_CHANGGIJIANCE t" +
				" left join flow_task f on f.main_id = t.id and f.table_name = 'GWM_CHANGGIJIANCE' left join (select t1.id,t1.FLOW_TASK_ID,row_number() over(partition by t1.FLOW_TASK_ID order by t1.id) r from FLOW_TASK_INFO t1 ) i" +
				" on i.FLOW_TASK_ID = f.id and  i.r=1 where t.id="+id;
		Query query = getSession().createSQLQuery(sql);
		List list = query.list();
		return list;
	}


	@Override
	public int getreportCount(String id) {
		String sql = "select count(*) from gwm_report_update u where u.re_id="+id;
		Query query = getSession().createSQLQuery(sql);
		List<Number> count = query.list();
		int total = Integer.parseInt(count.get(0).toString());
		return total;
	}
	
	/**
	 * 
	 * 描述：报告修改流程---->报告编辑节点 点击【查看报告】
	 * @author wangxd
	 * @date Sep 30, 2015 1:13:55 PM
	 * @return PageResult
	 * @throw
	 */
	public PageResult queryReportUpdateBgList(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append("select count(*)  FROM GWM_REPORT_UPDATE_BG T WHERE 1=1 ");
		queryString.append("select T.ID,T.FILE_NAME,T.REPORT_SHIBIEHAO,to_char(T.ADD_TIME,'yyyy-MM-dd'),T.ADD_USER_NAME,T.FILE_PATH FROM GWM_REPORT_UPDATE_BG T WHERE 1=1 ");
		if (filterMap!= null && !filterMap.isEmpty()) {
			if (!StringUtil.isMissing((String)filterMap.get("gwmItemSampleId"))){
				queryCountString.append("and t.GWM_ITEMSAMPLE_ID ='" + filterMap.get("gwmItemSampleId")+"'");
				queryString.append("and t.GWM_ITEMSAMPLE_ID ='" + filterMap.get("gwmItemSampleId")+"'");
			}
			if (!StringUtil.isMissing((String)filterMap.get("oldFlowtaskId"))){
				queryCountString.append("and t.OLD_FLOWTASKID ='" + filterMap.get("oldFlowtaskId")+"'");
				queryString.append("and t.OLD_FLOWTASKID ='" + filterMap.get("oldFlowtaskId")+"'");
			}
		    String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append(" order by " + sidx + " " + sord + " ");
		} 
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

}
